package unilearn.da;

import java.sql.*;
import java.util.Vector;
import javax.naming.*;

import unilearn.domain.AssessmentCriteriaValue;

/**
 * @author Yin Zhang, 5393558
 * @author Ben Morrall, 4158768
 * @version 0.1, 04 May 2008, initial creation
 * @version 0.2, 14 May 2008, Code Refractor, blm
 */
public class DAAssessmentCriteriaValue extends DABase
{

	public static Vector<AssessmentCriteriaValue> getCriteriaValueByAssessment(
			long assessmentID) throws SQLException, NamingException
	{
		Vector<AssessmentCriteriaValue> criteriaValues = new Vector<AssessmentCriteriaValue>();
		ResultSet rs = null;
		PreparedStatement ps = null;
		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("SELECT acv.assessment_criteria_id, acv.assessment_id, acv.grade, ac.display_order "
					+ "FROM assessment_criteria_value AS acv, assessment_criteria AS ac "
					+ "WHERE acv.assessment_id = ? "
					+ "AND acv.assessment_criteria_id = ac.assessment_criteria_id "
					+ "ORDER BY ac.display_order DESC");
			ps.setLong(1, assessmentID);

			rs = ps.executeQuery();

			while (rs.next())
			{
				AssessmentCriteriaValue acv = new AssessmentCriteriaValue(
						rs.getLong("assessment_criteria_id"), assessmentID);
				acv.setGrade(rs.getInt("grade"));
				criteriaValues.add(acv);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return criteriaValues;
	}

	public static void insertCriteriaValue(long criteriaID, long assessmentID, double grade)
		throws SQLException, NamingException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("INSERT INTO assessment_criteria_value"
					+ "(assessment_criteria_id, assessment_id, grade) " + "VALUES (?,?,?)");

			ps.setLong(1, criteriaID);
			ps.setLong(2, assessmentID);
			ps.setDouble(3, grade);

			ps.execute();
		}
		finally
		{
			if (ps != null) ps.close();
		}
	}

	public static void updateCriteriaValue(long criteriaID, long assessmentID, double grade)
		throws SQLException, NamingException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("UPDATE assessment_criteria_value SET "
					+ "grade = ? WHERE " + "assessment_criteria_id = ? AND assessment_id = ?");

			ps.setDouble(1, grade);
			ps.setLong(2, criteriaID);
			ps.setLong(3, assessmentID);

			ps.execute();
		}
		finally
		{
			if (ps != null) ps.close();
		}
	}

	/**
	 * Prevent Instances of DAAssessmentCriteriaValue
	 */
	private DAAssessmentCriteriaValue()
	{
	}
}